use master;
GO

ALTER DATABASE siscon SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

DROP DATABASE siscon;
GO
CREATE DATABASE siscon;
GO

USE siscon;

GO

CREATE TABLE sis_user (
    [user_id] [bigint] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [is_static] [bit] NOT NULL DEFAULT 0,
    [login_name] [nvarchar](30) COLLATE Modern_Spanish_CI_AS NOT NULL,
    [display_name] [nvarchar](30) COLLATE Modern_Spanish_CI_AS NOT NULL,
    [password] [varbinary](140) NOT NULL,
    [email] [nvarchar](300) COLLATE Modern_Spanish_CI_AS,
    [role_id] [bigint] NOT NULL,
    [is_active] [bit] NOT NULL,
    [created_by] [bigint] NOT NULL,
    [creation_date] [datetime] NOT NULL,
    [last_updated_by] [bigint] NOT NULL,
    [last_update_date] [datetime] NOT NULL
);

GO

CREATE TABLE sis_teacher (
	[teacher_id] [bigint] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) COLLATE Modern_Spanish_CI_AS NOT NULL,
	[school_id] [nvarchar](40) NOT NULL,
    [created_by] [bigint] NOT NULL
);

GO

CREATE TABLE sis_department (
	[department_id] [bigint] IDENTITY(1,1) NOT NULL,
	[department_code] [nvarchar](20) COLLATE Modern_Spanish_CI_AS NOT NULL,
	[department_name] [nvarchar](50) COLLATE Modern_Spanish_CI_AS NOT NULL
);

CREATE TABLE sis_classroom (
    [classroom_id] [bigint] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [classroom_location] [nvarchar](50) NOT NULL,
    [classroom_number] [nvarchar](50) NOT NULL,
    [created_by] [bigint] NOT NULL
);

GO

CREATE TABLE sis_role (
    [role_id] [bigint] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) COLLATE Modern_Spanish_CI_AS NOT NULL,
    [is_static] [bit] NOT NULL DEFAULT 0,
    [is_active] [bit] NOT NULL,
    [created_by] [bigint] NOT NULL,
    [creation_date] [datetime] NOT NULL,
    [last_updated_by] [bigint] NOT NULL,
    [last_update_date] [datetime] NOT NULL
);

GO

CREATE TABLE sis_role_det (
    [role_det_id] [bigint] IDENTITY(1,1) NOT NULL,
    [role_id] [bigint] NOT NULL,
    [resource] [nvarchar](50) COLLATE Modern_Spanish_CI_AS NOT NULL,
    [access_level] [char](1) NOT NULL
);

GO

CREATE TABLE sis_session (
    [session_id] [bigint] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [session_uuid] [uniqueidentifier] NOT NULL default newid(),
    [user_id] [nvarchar](80) COLLATE Modern_Spanish_CI_AS NOT NULL,
    [is_active] [bit] NOT NULL,
    [created_by] [bigint] NOT NULL,
    [creation_date] [datetime] NOT NULL,
	[last_updated_by] [bigint] NOT NULL,
    [last_update_date] [datetime] NOT NULL
);

GO

CREATE TABLE sis_configuration (
	[id] [bigint] IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[config_key] [varchar](100) NULL,
	[config_value] [varchar](800) NULL,
	[created_by] [bigint] NOT NULL,
	[creation_date] [datetime] NOT NULL,
	[last_updated_by] [bigint] NOT NULL,
	[last_update_date] [datetime] NOT NULL
);

GO

CREATE TABLE sis_error_log (
    [error_log_id] [bigint] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [thread] [nvarchar](255),
    [level] [nvarchar](50),
    [logger] [nvarchar](255),
    [message] [nvarchar](4000),
    [exception] [nvarchar](2000),
    [creation_date] [datetime] NOT NULL,
);

GO

CREATE TABLE sis_course (
	[course_id] [bigint] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [course_code] [nvarchar] (255) NOT NULL,
    [course_number] [nvarchar] (255) NOT NULL,
	[course_name] [nvarchar](255) NOT NULL,
    [is_active] [bit] NOT NULL,
    [created_by] [bigint] NOT NULL
);
	
GO

CREATE TABLE sis_schedule (
    [schedule_id] [bigint] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [days] [nvarchar] (255) NOT NULL,
    [starts_at] [datetime] NOT NULL,
    [ends_at] [datetime] NOT NULL,
    [classroom_location] [nvarchar](50) NOT NULL,
    [classroom_number] [nvarchar](50) NOT NULL,
    [created_by] [bigint] NOT NULL
);
    
GO

CREATE TABLE sis_course_teacher (
    [course_id] [bigint] NOT NULL,
    [school_id] [nvarchar] (40) NOT NULL,
    PRIMARY KEY(course_id, school_id) 
);
    
GO

CREATE TABLE sis_course_schedule (
    [course_id] [bigint] NOT NULL,
    [schedule_id] [bigint] NOT NULL,
    PRIMARY KEY(course_id, schedule_id)
);
    
GO